package com.jiuyun.dao.impl;

import com.jiuyun.dao.ClassesDao;
import com.jiuyun.entity.Classes;
import com.jiuyun.entity.Homework;
import com.jiuyun.entity.Student;
import com.jiuyun.util.Alipool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class ClassesDaoImpl implements ClassesDao {

    QueryRunner runner=new QueryRunner();

    @Override
    public List getClasses() throws SQLException {
        String sql=" select * from classes";
        Connection conn= Alipool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Classes>(Classes.class));
        conn.close();
        return list;
    }

    @Override
    public List getStudents(int classId) throws SQLException {
        String sql="select * from student where classId=?";
        Connection conn=Alipool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Student>(Student.class),classId);
        conn.close();
        return list;
    }

    @Override
    public List getHomework(int cid,int nian,int yue) throws SQLException {
        String sql="SELECT c.*,a.stname,SUBSTRING(c.ctime,9) ts " +
                "FROM student a " +
                "INNER JOIN classes b ON a.classId=b.classId " +
                "INNER JOIN homework c ON a.stid=c.studentId " +
                "WHERE b.classId=? and YEAR(c.ctime)=? and MONTH(c.ctime)=?";
        Connection conn=Alipool.getConn();
        List list=runner.query(conn,sql,new BeanListHandler<Homework>(Homework.class),cid,nian,yue);
        conn.close();
        return list;
    }

    @Override
    public int add(int stid, String date, String bz) throws SQLException {
        Connection conn=Alipool.getConn();
        String sql="INSERT into homework VALUES(null,?,1,?,?);";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setInt(1,stid);
        pst.setString(2,date);
        pst.setString(3,bz);
        int as=pst.executeUpdate();
        if (as>0){
            return 1;
        }else{
            return 0;
        }


    }
}
